While performing on operations on table, error there isn't enough disk space or memory if operation creates a large number of page locks. Error will come if it exceeds the MaxLocksPerFile value. By default MaxLocksPerFile value is 9500. If any operation, creates page locks greater then 9500, then this error will come.

If you run an action query on a large table, you may receive the following error message: There isn't enough disk space or memory to undo the data changes this action query is about to make. The cause of error is same, exceed from MaxLocksPerFile value. To solve this, firstly we have to generate this error, for this we need a database records greater than 9500. When we have to try performing delete all operation, the error will generated as shown in fig 1.1.

Cause: The page locks required for the transaction exceed the MaxLocksPerFile value, which defaults to 9500 locks. The MaxLocksPerFile setting is stored in the Windows registry.

Resolution: We have two ways to solve this error. One is temporary and second is permanent.

Temporary solution: We can write in VBA code editor-

DBEngine.SetOption dbMaxLocksPerFile, (e.g. DBEngine.SetOption dbMaxLocksPerFile, 200000 It will be set 200000 max locks), actually it increased the limit of locking the file at a time after it we can update or delete 200000 records at a time In current DB.

Permanent solution : We can change the value of key in registry of widows first open Registry Edit form RUN window by type regedit command and then go to this folder -HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile and change the value of MaxLocksPerFile key as many as you want to increase. When we open the registry, the default value is 9500 as shown in Fig 1.2.

We can change the default value of max locks per file as shown in fig 1.3. After changing this value we can perform operations on large tables.

VBA code to reset the value:

Option Compare Database
Function CreateBigTable()
Dim db As Database, rs As Recordset
Dim iCounter As Integer, strChar As String
Set db = CurrentDb
db.Execute "CREATE TABLE BigTable (ID LONG, Field1 TEXT(255),Field2 TEXT(255), Field3 TEXT(255), Field4 TEXT(255))", dbFailOnError
Set rs = db.OpenRecordset("BigTable", dbOpenDynaset)
DBEngine.SetOption dbMaxLocksPerFile, 200000 ' set max locks per file
iCounter = 0
strChar = String(255, " ")
While iCounter <= 10000
rs.AddNew
rs!ID = iCounter
rs!Field1 = strChar
rs!Field2 = strChar
rs!Field3 = strChar
rs!Field4 = strChar
rs.Update
iCounter = iCounter + 1
Wend
MsgBox "Done!"
End Function